﻿using Microsoft.WindowsAPICodePack.Dialogs;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Tian.Common.Texts;
using Tian.Common.Verify;
using Tian.OperationFile.excel;

namespace software.winwpfs.develop
{
    /// <summary>
    /// CreateSqlByExcel.xaml 的交互逻辑
    /// </summary>
    public partial class CreateSqlByExcel : Page
    {
        public CreateSqlByExcel()
        {
            InitializeComponent();
        }
        private void ButChooseFile(object sender, RoutedEventArgs e)
        {
            CommonOpenFileDialog dialog = new CommonOpenFileDialog("请选择一个文件夹");
            dialog.EnsureReadOnly = true;
            dialog.Filters.Add(new CommonFileDialogFilter("excel2007", "*.xlsx"));
            dialog.Filters.Add(new CommonFileDialogFilter("excel2003", "*.xls"));
            if (dialog.ShowDialog() == CommonFileDialogResult.Ok)
            {
                if (string.IsNullOrEmpty(dialog.FileName))
                {
                    MessageBox.Show("文件夹路径不能为空", "提示");
                    return;
                }
                FirstPath.Text = dialog.FileName;
                DataTable dt = EpplusUtil.ExcelToDataTable("mysheet", FirstPath.Text);
                ExcelTable.ItemsSource = dt.DefaultView;
            }
        }
        private void ButCreateSql(object sender, RoutedEventArgs e)
        {
            string tableName = TableName.Text.Trim();
            DataTable table = (ExcelTable.ItemsSource as DataView).ToTable();
            List<string> sqllist = new List<string>();
            int dt_column_count = table.Columns.Count;
            int dt_row_count = table.Rows.Count;
            List<string> columns = new List<string>();//excel中字段列
            for (int i = 0; i < dt_column_count; i++)
            {
                columns.Add(table.Columns[i].ColumnName);
            }
            if (InsertCheck.IsChecked == true)
            {
                for (int i = 0; i < dt_row_count; i++)
                {
                    List<string> values = new List<string>();
                    for (int j = 0; j < dt_column_count; j++)
                    {
                        values.Add(table.Rows[i][j].ToString());
                    }
                    //拼写sql
                    string sql_one = string.Format("insert into {0}({1}) values('{2}');", tableName, string.Join(",", columns)
                        , string.Join("','", values));
                    sqllist.Add(sql_one);
                }
            }
            if (UpdateCheck.IsChecked == true)
            {
                string tableKey = TableKey.Text.Trim();
                if (CharVerify.IsEmpty(tableKey))
                {
                    HandyControl.Controls.MessageBox.Error("主键不能为空！", "操作提示");
                    return;
                }
                List<string> tableKeys = new List<string>();
                tableKeys.AddRange(tableKey.Split(","));
                //移除条件字段
                for (int i = 0; i < columns.Count; i++)
                {
                    if (tableKeys.Contains(columns[i]))
                    {
                        columns.Remove(columns[i]);
                    }
                }
                //创建更新语句
                for (int i = 0; i < dt_row_count; i++)
                {
                    string setValue = "set";
                    for (int j = 0; j < columns.Count; j++)
                    {
                        setValue += string.Format(" {0} = '{1}',", columns[j], table.Rows[i][columns[j]].ToString());
                    }
                    setValue = StringUtil.RemoveLastChar(setValue);
                    string setWhere = " where";
                    for (int j = 0; j < tableKeys.Count; j++)
                    {
                        setWhere += string.Format(" and {0} = '{1}'", tableKeys[j], table.Rows[i][tableKeys[j]].ToString());
                    }
                    setWhere = setWhere.Remove(7, 3);
                    string sql_one = string.Format("update {0} {1} {2};", tableName
                    , setValue, setWhere);
                    sqllist.Add(sql_one);
                }
            }
            //写入文本及导出
            string lujing = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "插入语句.txt");
            File.WriteAllLines(lujing, sqllist);
            MessageBoxResult messageBoxResult = HandyControl.Controls.MessageBox.Show("已生成，需要打开吗？", "打开文件", MessageBoxButton.YesNo, MessageBoxImage.Question);
            if (messageBoxResult == MessageBoxResult.Yes)
            {
                ProcessStartInfo startInfo = new ProcessStartInfo
                {
                    FileName = lujing,
                    UseShellExecute = true
                };
                Process.Start(startInfo);
            }
        }

        private void UpdateCheck_Checked(object sender, RoutedEventArgs e)
        {
            if (InsertCheck.IsChecked == true)
            {
                InsertCheck.IsChecked = false;
            }
        }

        private void InsertCheck_Checked(object sender, RoutedEventArgs e)
        {
            if (UpdateCheck.IsChecked == true)
            {
                UpdateCheck.IsChecked = false;
            }
        }
    }
}
